const { db, sqlExec } = require('../db/mysql57');

const schoolDao = {
    /*
     * 获取所有院校
     */
    async getAllSchool() {
        let sql = `SELECT
                c.id as id,
                c.name as name,
                c.iconId as iconId,
                p.id as pid,
                p.name as pname,
                (select count(1) from AdmissionPlan where collegeId = c.id) as majorCount,
                (select sum(admissionQuota) from AdmissionPlan where collegeId = c.id) as studentCount
            FROM College as c
            LEFT JOIN Province as p ON c.provinceId = p.id`;
        let data = [];
        return sqlExec(sql, data);
    },
    /*
     * 收藏院校
     */
    async collectSchool(uid, collegeId) {
        let sql = `INSERT INTO CollegeCollection (uid, collegeId) (
            select ?, ? where (
                select count(*) from CollegeCollection where uid = ? and collegeId = ?
            ) = 0
        )`;
        let data = [ uid, collegeId, uid, collegeId ];
        return sqlExec(sql, data);
    },
    /*
     * 取消收藏院校
     */
    async noCollectSchool(uid, collegeId) {
        let sql = `delete from CollegeCollection where uid = ? and collegeId = ?`;
        let data = [ uid, collegeId, uid, collegeId ];
        return sqlExec(sql, data);
    },
    /*
     * 懒加载获取收藏院校接口
     */
    async getCollectSchool(uid, lastId) {
        let sql = `select
                c.id as id,
                cc.id as ccid,
                c.name as name,
                c.iconId as iconId,
                p.id as pid,
                p.name as pname,
                (select count(1) from AdmissionPlan where collegeId = c.id) as majorCount,
                (select sum(admissionQuota) from AdmissionPlan where collegeId = c.id) as studentCount
            from CollegeCollection as cc
            left join College as c on cc.collegeId = c.id
            left join Province as p on c.provinceId = p.id
            where cc.uid = ?
            LIMIT 10`;
        let data = [ uid ];
        if (lastId) {
            sql = `select
                c.id as id,
                cc.id as ccid,
                c.name as name,
                c.iconId as iconId,
                p.id as pid,
                p.name as pname,
                (select count(1) from AdmissionPlan where collegeId = c.id) as majorCount,
                (select sum(admissionQuota) from AdmissionPlan where collegeId = c.id) as studentCount
            from CollegeCollection as cc
            left join College as c on cc.collegeId = c.id
            left join Province as p on c.provinceId = p.id
            where cc.uid = ? and cc.id > ?
            LIMIT 10`;
            data.push(lastId);
        }
        return sqlExec(sql, data);
    },
    /*
     * 获取所有专业
     */
    async getAllMajor(collegeId) {
        let sql = `select
                id, major as name,
                testMethod as examType,
                studyMethod as studyType,
                admissionQuota as enrollmentNumber
            from AdmissionPlan where collegeId = ?`;
        let data = [ collegeId ];
        return sqlExec(sql, data);
    },
    /*
     * 获取院校资讯内容
     */
    async getAllzixun(collegeId, lastId) {
        let sql = `select
                    n.id as id,
                    n.title as title,
                    DATE_FORMAT(n.time, '%Y-%m-%d %H:%i:%s') as time,
                    c.name as school
                from News as n
                left join College as c on n.collegeId = c.id
                where n.collegeId = ?
                ORDER BY time DESC
                LIMIT 10`;
        let data = [ collegeId ];
        if (lastId) {
            sql = `select
                    n.id as id,
                    n.title as title,
                    DATE_FORMAT(n.time, '%Y-%m-%d %H:%i:%s') as time,
                    c.name as school
                from News as n
                left join College as c on n.collegeId = c.id
                where n.collegeId = ? and n.time <= (
                    select nn.time from News as nn where nn.id = ?
                ) and n.id != ?
                ORDER BY time DESC
                LIMIT 10`;
            data = [ collegeId, lastId, lastId ];
        }
        return sqlExec(sql, data);
    },
    /*
     * 获取所有资讯
     */
    async getRecommendzixun(lastId) {
        let sql = `select
                    n.id as id,
                    n.title as title,
                    DATE_FORMAT(n.time, '%Y-%m-%d %H:%i:%s') as time,
                    c.name as school
                from News as n
                left join College as c on n.collegeId = c.id
                ORDER BY time DESC
                LIMIT 10`;
        let data = [];
        if (lastId) {
            sql = `select
                    n.id as id,
                    n.title as title,
                    DATE_FORMAT(n.time, '%Y-%m-%d %H:%i:%s') as time,
                    c.name as school
                from News as n
                left join College as c on n.collegeId = c.id
                where n.time <= (
                    select nn.time from News as nn where nn.id = ? LIMIT 1
                ) and n.id != ?
                ORDER BY time DESC
                LIMIT 10`;
            data = [ lastId, lastId ];
        }
        return sqlExec(sql, data);
    },
    // 获取院校详细信息
    async getSchoolInfo(uid, collegeId) {
        let sql = `select
                c.id as id,
                c.name as name,
                p.name as city,
                c.tag as tag,
                (
                    select count(1)
                    from CollegeCollection as cc
                    where cc.uid = ? and cc.collegeId = ?
                ) as isCollect,
                c.profile as introduction,
                c.email as email,
                c.addressList as positionList,
                c.iconId as iconId
            from College as c
            left join Province as p on c.provinceId = p.id
            where c.id = ?`;
        let data = [ uid, collegeId, collegeId ];
        return sqlExec(sql, data);
    },
    /*
     * 获取资讯内容
     */
    async getzixunContent(zixunId) {
        let sql = `select
                title, content
            from News
            where id = ?`;
        let data = [ zixunId ];
        return sqlExec(sql, data);
    },
    async loadAllSchool(page) {
        let sql = `select
                c.id as id,
                c.name as name,
                c.profile as profile,
                (select p.name from Province as p where p.id = c.provinceId) as province,
                c.email as email,
                c.addressList as addressList,
                (select count(1) from AdmissionPlan where collegeId = c.id) as majorCount,
                (select sum(admissionQuota) from AdmissionPlan where collegeId = c.id) as studentCount
            from College as c
            LIMIT ?, 50`;
        let data = [ (page-1) * 50 ];
        return sqlExec(sql, data);
    }
};

module.exports = schoolDao;